using B.S.XZYData.Api.Read.Application.Command.Approval;
using B.S.XZYData.Domain.RBAC;
using B.S.XZYData.Domain.ZSH;
using B.S.XZYData.ErrorCode;
using B.S.XZYData.Infrastructure;
using MediatR;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;
using SqlSugar;
using System.Data;
using Microsoft.Extensions.Logging;
using B.S.XZYData.Infrastructure.Interface;

namespace B.S.XZYData.Api.Read.Application.Handler.Approval
{
    /// <summary>
    /// 查询所有审批列表Handler
    /// </summary>
    public class QueryPendingApprovalsHandler : IRequestHandler<QueryPendingApprovalsCommand, ApiResult<PendingApprovalPageResult>>
    {
        private readonly IBaseRepository<WorkflowInstanceModel> _workflowInstanceRepo;
        private readonly ILogger<QueryPendingApprovalsHandler> _logger;
        private readonly IConfiguration _configuration;

        public QueryPendingApprovalsHandler(
            IBaseRepository<WorkflowInstanceModel> workflowInstanceRepo, 
            ILogger<QueryPendingApprovalsHandler> logger,
            IConfiguration configuration)
        {
            _workflowInstanceRepo = workflowInstanceRepo;
            _logger = logger;
            _configuration = configuration;
        }

        public async Task<ApiResult<PendingApprovalPageResult>> Handle(QueryPendingApprovalsCommand request, CancellationToken cancellationToken)
        {
            var res = new ApiResult<PendingApprovalPageResult>
            {
                Code = ApiEnum.Fail,
                Msg = "查询失败",
                Data = null
            };

            try
            {
                // 获取连接字符串并添加MySQL零日期处理参数
                var connectionString = _configuration.GetConnectionString("db") + "Convert Zero Datetime=True;Allow Zero Datetime=True;";
                
                // 构建原生SQL查询，使用正确的表名
                var baseSql = @"
                    SELECT 
                        Id, WorkflowId, WorkOrderCode, WorkOrderNo, ProcessName, ProcessCode, 
                        Title, ApplicantUserName, StepName, AuditUserName, Status, FormData,
                        CreateTime, CompletedTime
                    FROM workflowinstancemodel 
                    WHERE 1=1";

                var whereConditions = new List<string>();
                var parameters = new List<MySqlParameter>();
                
                // 添加当前审批人条件
                if (!string.IsNullOrEmpty(request.CurrentAuditUserId))
                {
                    whereConditions.Add(" AND AuditUserId = @CurrentAuditUserId");
                    parameters.Add(new MySqlParameter("@CurrentAuditUserId", request.CurrentAuditUserId));
                }

                // 工单编号（同时匹配 WorkOrderCode 与 WorkOrderNo，模糊查询）
                if (!string.IsNullOrWhiteSpace(request.WorkOrderCode))
                {
                    whereConditions.Add(" AND (WorkOrderCode LIKE @WorkOrderCode OR WorkOrderNo LIKE @WorkOrderCode)");
                    parameters.Add(new MySqlParameter("@WorkOrderCode", $"%{request.WorkOrderCode}%"));
                }

                // 工序名称（精确匹配）
                if (!string.IsNullOrWhiteSpace(request.ProcessName))
                {
                    whereConditions.Add(" AND ProcessName = @ProcessName");
                    parameters.Add(new MySqlParameter("@ProcessName", request.ProcessName));
                }

                // 申请人（精确匹配）
                if (!string.IsNullOrWhiteSpace(request.ApplicantUserName))
                {
                    whereConditions.Add(" AND ApplicantUserName = @ApplicantUserName");
                    parameters.Add(new MySqlParameter("@ApplicantUserName", request.ApplicantUserName));
                }

                // 申请时间区间
                if (request.CreateTimeStart.HasValue)
                {
                    whereConditions.Add(" AND CreateTime >= @CreateTimeStart");
                    parameters.Add(new MySqlParameter("@CreateTimeStart", request.CreateTimeStart.Value));
                }
                if (request.CreateTimeEnd.HasValue)
                {
                    whereConditions.Add(" AND CreateTime <= @CreateTimeEnd");
                    parameters.Add(new MySqlParameter("@CreateTimeEnd", request.CreateTimeEnd.Value));
                }

                // 状态筛选：只有前端明确传入 Status 参数时才过滤，否则显示所有状态
                if (!string.IsNullOrWhiteSpace(request.Status))
                {
                    whereConditions.Add(" AND Status = @Status");
                    parameters.Add(new MySqlParameter("@Status", request.Status));
                }

                var whereClause = string.Join("", whereConditions);
                var finalSql = baseSql + whereClause + " ORDER BY CreateTime DESC";

                // 使用原生MySqlConnection完全避开SqlSugar
                using (var connection = new MySqlConnection(connectionString))
                {
                    await connection.OpenAsync();
                    
                    // 查询总数
                    var countSql = "SELECT COUNT(*) FROM workflowinstancemodel WHERE 1=1" + whereClause;
                    using (var countCommand = new MySqlCommand(countSql, connection))
                    {
                        foreach (var p in parameters)
                        {
                            countCommand.Parameters.Add(new MySqlParameter(p.ParameterName, p.Value));
                        }
                        var totalCount = Convert.ToInt32(await countCommand.ExecuteScalarAsync());
                        var totalPages = (int)Math.Ceiling((double)totalCount / request.PageSize);

                        // 分页查询数据
                        var offset = (request.PageIndex - 1) * request.PageSize;
                        var pagingSql = finalSql + $" LIMIT {request.PageSize} OFFSET {offset}";

                        using (var dataCommand = new MySqlCommand(pagingSql, connection))
                        {
                            foreach (var p in parameters)
                            {
                                dataCommand.Parameters.Add(new MySqlParameter(p.ParameterName, p.Value));
                            }
                            using (var reader = await dataCommand.ExecuteReaderAsync())
                            {
                                var dataList = new List<PendingApprovalDto>();
                                while (await reader.ReadAsync())
                                {
                                    var dto = new PendingApprovalDto
                                    {
                                        Id = reader.GetInt32("Id"),
                                        WorkflowId = reader.IsDBNull("WorkflowId") ? "" : reader.GetString("WorkflowId"),
                                        WorkOrderCode = reader.IsDBNull("WorkOrderCode") ? "" : reader.GetString("WorkOrderCode"),
                                        WorkOrderNo = reader.IsDBNull("WorkOrderNo") ? "" : reader.GetString("WorkOrderNo"),
                                        ProcessName = reader.IsDBNull("ProcessName") ? "" : reader.GetString("ProcessName"),
                                        ProcessCode = reader.IsDBNull("ProcessCode") ? "" : reader.GetString("ProcessCode"),
                                        Title = reader.IsDBNull("Title") ? "" : reader.GetString("Title"),
                                        ApplicantUserName = reader.IsDBNull("ApplicantUserName") ? "" : reader.GetString("ApplicantUserName"),
                                        StepName = reader.IsDBNull("StepName") ? "" : reader.GetString("StepName"),
                                        AuditUserName = reader.IsDBNull("AuditUserName") ? "" : reader.GetString("AuditUserName"),
                                        Status = reader.IsDBNull("Status") ? "" : reader.GetString("Status"),
                                        FormData = reader.IsDBNull("FormData") ? null : reader.GetString("FormData"),
                                        CreateTime = reader.IsDBNull("CreateTime") ? DateTime.MinValue : reader.GetDateTime("CreateTime"),
                                        CompletedTime = reader.IsDBNull("CompletedTime") ? (DateTime?)null : reader.GetDateTime("CompletedTime")
                                    };
                                    dataList.Add(dto);
                                }

                                res.Data = new PendingApprovalPageResult
                                {
                                    TotalCount = totalCount,
                                    PageIndex = request.PageIndex,
                                    PageSize = request.PageSize,
                                    TotalPages = totalPages,
                                    Data = dataList
                                };

                                res.Code = ApiEnum.Success;
                                res.Msg = "查询成功";
                            }
                        }
                    }
                }

                return res;
            }
            catch (Exception ex)
            {
                _logger.LogError(ex, "查询待审批列表失败");
                res.Code = ApiEnum.Fail;
                res.Msg = "查询失败：" + ex.Message;
                return res;
            }
        }
    }
} 